using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using System.Data;

namespace DbfToAccess
{
    public static class Tools
    {

        public static int JenisKelamin(string kode)
        {
            int ret = 0;

            if (kode == "L")
            {
                ret = 1;
            }
            else if (kode == "P")
            {
                ret = 0;
            }
            return ret;
        }

        public static int KodeStatus(string kode)
        {
            int status = 0;

            if (kode == "TK")
                status = 1;
            else if (kode == "K0")
                status = 2;
            else if (kode == "K1")
                status = 2;
            else if (kode == "K2")
                status = 2;
            else if (kode == "K3")
                status = 2;

            return status;
        }

        public static int JmlTanggungan(string kode)
        {
            int status = 0;
            if (kode == "TK")
                status = 0;
            else if (kode == "K0")
                status = 0;
            else if (kode == "K1")
                status = 1;
            else if (kode == "K2")
                status = 2;
            else if (kode == "K3")
                status = 3;

            return status;
        }

        public static long RemoveDelimeter(string npwp)
        {
            //49.321.351.6-016.000
            long noNPWP = 0;

            if (npwp != string.Empty)
            {
                string[] tmp = npwp.Split(".".ToCharArray());
                string gabung1 = string.Empty;
                string gabung2 = string.Empty;

                for (int i = 0; i < tmp.Length; i++)
                {
                    gabung1 += tmp[i];
                }

                string[] tmp2 = gabung1.Split("-".ToCharArray());

                for (int i = 0; i < tmp2.Length; i++)
                {
                    gabung2 += tmp2[i];
                }
                noNPWP = Convert.ToInt64(gabung2);
            }

            return noNPWP;
        }

        public static string JoinWithoutDot(string npwp)
        {
            //49.321.351.6-016.000
            string noNPWP = string.Empty;

            if (npwp != string.Empty)
            {
                string[] tmp = npwp.Split(".".ToCharArray());
                string gabung1 = string.Empty;
                string gabung2 = string.Empty;

                for (int i = 0; i < tmp.Length; i++)
                {
                    //if(tmp[0].Substring(0,1))
                    gabung1 += tmp[i];
                }

                string[] tmp2 = gabung1.Split("-".ToCharArray());

                for (int i = 0; i < tmp2.Length; i++)
                {
                    gabung2 += tmp2[i];
                }
                noNPWP = gabung2;
            }

            return noNPWP;
        }

        public static string RemovePetik(string word)
        {
            string tmp = word.Replace("'", "~");

            return tmp;
        }

        public static int GetLastNumber(string field, string table, string PathAccess)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();

            string strIsExist = "select count(" + field + ") from " + table + "";
            string strQuery = string.Empty;
            bool isExist = false;
            int max = 0;
            int val = 0;

            OleDbCommand cmdExist = con.CreateCommand();
            cmdExist.CommandText = strIsExist;
            cmdExist.CommandType = CommandType.Text;

            OleDbDataReader rdExist = cmdExist.ExecuteReader();

            while (rdExist.Read())
            {
                val = Convert.ToInt32(rdExist[0]);
                isExist = true;
            }

            if (isExist)
            {
                if (val > 0)
                {
                    strQuery = "select max(" + field + ") from " + table + "";

                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strQuery;
                    cmd.CommandType = CommandType.Text;

                    OleDbDataReader aReader = cmd.ExecuteReader();

                    while (aReader.Read())
                    {
                        max = Convert.ToInt32(aReader[0]);
                    }

                    max = max + 1;

                    cmd.Dispose();
                    con.Close();
                }
                else
                {
                    max = 1;
                }
            }

            return max;
        }

        public static bool IsHasData(string field, string table, string pathAccess)
        {
            bool isExist = false;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();
            string strIsExist = string.Empty;

            if (field != string.Empty)
            {
                strIsExist = "select count(" + field + ") from " + table + "";
            }
            else
            {
                strIsExist = "select count(*) from " + table + "";
            }

            string strQuery = string.Empty;
            int val = 0;

            OleDbCommand cmdExist = con.CreateCommand();
            cmdExist.CommandText = strIsExist;
            cmdExist.CommandType = CommandType.Text;

            OleDbDataReader rdExist = cmdExist.ExecuteReader();
            
            while (rdExist.Read())
            {
                val = Convert.ToInt32(rdExist[0]);
            }

            if (val > 0)
            {
                isExist = true;
            }

            con.Close();
            con.Dispose();

            return isExist;
        }
    }
}
